﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TarreOA.TarreOAModels;
using System.Configuration;

namespace TarreOA.TarreOADAL
{
    public class SectionDAO
    {
        //查询所有部门信息
        public List<TarreOAModels.Section> findAllSections()
        {
            List<TarreOAModels.Section> list = new List<TarreOA.TarreOAModels.Section>();
            SqlConnection conn = null;
            SqlDataReader dr = null;
            try
            {
                conn = SQLManager.GetConn();
                conn.Open();
                SqlCommand cmd = new SqlCommand("select * from sectiontb", conn);
                dr = cmd.ExecuteReader();
                while(dr.Read())
                {
                    Section section = new Section();
                    section.SectionId = int.Parse(dr["SectionId"].ToString());
                    section.SectionName = dr["SectionName"].ToString();
                    section.Telephone = dr["Telephone"].ToString();
                    section.Remark = dr["Remark"].ToString();
                    section.Facsimile = dr["Facsimile"].ToString();
                    section.Address = dr["Address"].ToString();
                    section.BaseSection = findBySectionId((int)dr["BaseSection"]);
                    section.CurrentPlace = (int)dr["CurrentPlace"];
                    list.Add(section);
                }
                dr.Close();
                conn.Close();
                dr.Dispose();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
            }
            finally
            {
                if (!dr.IsClosed)
                {
                    dr.Close();
                    dr.Dispose();
                }
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return list;
        }

        //按部门编号查询部门信息
        public Section findBySectionId(int sectionId)
        {
            count++;
            Section section = new Section();
            SqlConnection conn = null;
            SqlDataReader dr = null;
            try
            {
                conn = SQLManager.GetConn();
                conn.Open();
                SqlCommand cmd = new SqlCommand("select * from sectiontb where SectionId = @SectionId", conn);
                cmd.Parameters.Add(new SqlParameter("@SectionId", sectionId));
                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        section.SectionId = (int)dr["SectionId"];
                        section.SectionName = dr["SectionName"].ToString();
                        section.Telephone = dr["Telephone"].ToString();
                        section.Remark = dr["Remark"].ToString();
                        section.Facsimile = dr["Facsimile"].ToString();
                        section.Address = dr["Address"].ToString();
                        section.CurrentPlace = (int)dr["CurrentPlace"];
                        section.BaseSection = findBySectionId((int)dr["BaseSection"]);
                        if ((int)dr["BaseSection"] == 0)
                        {
                            break;
                        }
                    }
                }
                dr.Close();
                conn.Close();
                dr.Dispose();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
            }
            finally
            {
                if (!dr.IsClosed)
                {
                    dr.Close();
                    dr.Dispose();
                }
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return section;
        }

        //新增部门信息
        public Boolean createSection(string sectionName, string telephone, int baseSection, string facsimile, string remark, string address, int currentPlace)
        {
            bool state = false;
            SqlConnection conn = null;
            try
            {
                conn = SQLManager.GetConn();
                conn.Open();
                SqlCommand cmd = new SqlCommand("insert into Sectiontb values(@SectionName, @telephone, @baseSection, @facsimile, @remark, @address, @currentPlace)", conn);
                cmd.Parameters.Add(new SqlParameter("@SectionName",sectionName));
                cmd.Parameters.Add(new SqlParameter("@telephone", telephone));
                cmd.Parameters.Add(new SqlParameter("@baseSection", baseSection));
                cmd.Parameters.Add(new SqlParameter("@facsimile", facsimile));
                cmd.Parameters.Add(new SqlParameter("@remark", remark));
                cmd.Parameters.Add(new SqlParameter("@address", address));
                cmd.Parameters.Add(new SqlParameter("@currentPlace", currentPlace));
                if (cmd.ExecuteNonQuery() == 1)
                {
                    state = true;
                }
                conn.Close();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return state;
        }

        //根据上级部门编号查询部门信息
        private int count = 0;
        public List<Section> findByBaseSection(int baseSectionId)
        {
            SqlConnection conn = null;
            SqlDataReader dr = null;
            List<Section> list = new List<Section>();
            try
            {
                conn = SQLManager.GetConn();
                conn.Open();
                SqlCommand cmd = new SqlCommand("select * from Sectiontb where baseSection = @baseSectionId order by CurrentPlace asc", conn);
                cmd.Parameters.Add(new SqlParameter("@baseSectionId", baseSectionId));
                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        Section section = new Section();
                        section.SectionId = int.Parse(dr["SectionId"].ToString());
                        section.SectionName = dr["SectionName"].ToString();
                        section.Telephone = dr["Telephone"].ToString();
                        section.Remark = dr["Remark"].ToString();
                        section.Facsimile = dr["Facsimile"].ToString();
                        section.Address = dr["Address"].ToString();
                        section.BaseSection = findBySectionId((int)dr["BaseSection"]);
                        list.Add(section);
                    }
                }
                dr.Close();
                conn.Close();
                dr.Dispose();
                conn.Dispose();
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (!dr.IsClosed)
                {
                    dr.Close();
                    dr.Dispose();
                }
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return list;
        }

        //根据部门编号删除部门信息
        public int delSectionInfo(int sectionId)
        {
            int state = 0;
            SqlConnection conn = null;
            try
            {
                conn = SQLManager.GetConn();
                conn.Open();
                SqlCommand cmd = new SqlCommand("delete from Sectiontb where SectionId = @SectionId", conn);
                cmd.Parameters.Add(new SqlParameter("@SectionId", sectionId));
                state = cmd.ExecuteNonQuery();
                conn.Close();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if(conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return state;
        }

        //删除部门后将同部门下的其他部门位置更新
        //public bool upOtherSectionPlace(int baseSectionId, int curPlace)
        //{
        //    bool state = false;
        //    SqlConnection conn = null;
        //    try
        //    {
        //        conn = SQLManager.GetConn();
        //        conn.Open();
        //        SqlCommand cmd = new SqlCommand("update Sectiontb set CurrentPlace = CurrentPlace - 1 where BaseSection = @baseSectionId and CurrentPlace > @curPlace", conn);
        //        cmd.Parameters.Add(new SqlParameter("@baseSectionId", baseSectionId));
        //        cmd.Parameters.Add(new SqlParameter("@curPlace", curPlace));
        //        cmd.ExecuteNonQuery();
        //        state = true;
        //        conn.Close();
        //        conn.Dispose();
        //    }
        //    catch (Exception ex)
        //    {
        //        Console.WriteLine(ex.Message);
        //    }
        //    finally
        //    {
        //        if (conn.State == ConnectionState.Open)
        //        {
        //            conn.Close();
        //            conn.Dispose();
        //        }
        //    }
        //    return state;
        //}

        //根据部门编号更改部门信息
        public int updateSectionInfo(int sectionId, string sectionName, string telephone, int baseSectionId, string facsimile, string remark, string address)
        {
            int count = 0;
            SqlConnection conn = null;
            try
            {
                conn = SQLManager.GetConn();
                conn.Open();
                string sql = "update Sectiontb set SectionName = @SectionName, Telephone = @telephone, BaseSection = @baseSectionid,"
                    + " Facsimile = @facsimile, Remark = @remark, Address = @address where SectionId = @SectionId";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@SectionName", sectionName));
                cmd.Parameters.Add(new SqlParameter("@telephone", telephone));
                cmd.Parameters.Add(new SqlParameter("@baseSectionid", baseSectionId));
                cmd.Parameters.Add(new SqlParameter("@facsimile", facsimile));
                cmd.Parameters.Add(new SqlParameter("@remark", remark));
                cmd.Parameters.Add(new SqlParameter("@address", address));
                cmd.Parameters.Add(new SqlParameter("@SectionId", sectionId));
                count = cmd.ExecuteNonQuery();
                conn.Close();
                conn.Dispose();
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return count;
        }

        //根据上级部门编号返回最后位置索引
        //public int findPlaceByBaseSectionId(int baseSectionId)
        //{
        //    int index = 0;
        //    SqlConnection conn = null;
        //    try
        //    {
        //        conn = SQLManager.GetConn();
        //        conn.Open();
        //        SqlCommand cmd = new SqlCommand("select max(CurrentPlace) from Sectiontb where BaseSection = @baseSectionId", conn);
        //        cmd.Parameters.Add(new SqlParameter("@baseSectionId", baseSectionId));
        //        index = (int)cmd.ExecuteScalar();
        //        conn.Close();
        //        conn.Dispose();
        //    }
        //    catch (Exception ex)
        //    {
        //        Console.WriteLine(ex.Message);
        //    }
        //    finally
        //    {
        //        if (conn.State == ConnectionState.Open)
        //        {
        //            conn.Close();
        //            conn.Dispose();
        //        }
        //    }
        //    return index;
        //}

        //更改部门位置
        public void changeSectionCurrentPlace(int baseSectionId, int sectionId, int curPlace, string action)
        {
            SqlConnection conn = null;
            try
            {
                conn = SQLManager.GetConn();
                conn.Open();
                if ("up".Equals(action))
                {
                    string sql = "update sectiontb set currentPlace = currentPlace + 1 where sectionid = (select sectionid from sectiontb where basesection = @baseSectionId and currentPlace = @curPlace - 1) "
                                + "update sectiontb set currentPlace = currentPlace - 1 where sectionid = (select sectionid from sectiontb where basesection = @baseSectionId and SectionId = @SectionId)";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.Parameters.Add(new SqlParameter("@baseSectionId", baseSectionId));
                    cmd.Parameters.Add(new SqlParameter("@curPlace", curPlace));
                    cmd.Parameters.Add(new SqlParameter("@SectionId", sectionId));
                    cmd.ExecuteNonQuery();
                }
                else
                {
                    string sql = "update sectiontb set currentPlace = currentPlace - 1 where sectionid = (select sectionid from sectiontb where basesection = @baseSectionId and currentPlace = @curPlace + 1) "
                                + "update sectiontb set currentPlace = currentPlace + 1 where sectionid = (select sectionid from sectiontb where basesection = @baseSectionId and SectionId = @SectionId)";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.Parameters.Add(new SqlParameter("@baseSectionId", baseSectionId));
                    cmd.Parameters.Add(new SqlParameter("@curPlace", curPlace));
                    cmd.Parameters.Add(new SqlParameter("@SectionId", sectionId));
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }
        /// <summary>
        /// 根据ID获取部门信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Section FindSectionById(int id)
        {
            Section section = new Section();
            SqlConnection conn = null;
            SqlDataReader dr = null;
            try
            {
                conn = SQLManager.GetConn();
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "select * from Sectiontb where SectionId=" + id.ToString();
                dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    section.SectionId = int.Parse(dr["SectionId"].ToString());
                    section.SectionName = dr["SectionName"].ToString();
                    section.Telephone = dr["Telephone"].ToString();
                    section.Remark = dr["Remark"].ToString();
                    section.Facsimile = dr["Facsimile"].ToString();
                    section.Address = dr["Address"].ToString();
                    section.BaseSection = findBySectionId((int)dr["BaseSection"]);
                    section.CurrentPlace = (int)dr["CurrentPlace"];
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                    dr.Dispose();
                }
                if (conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return section;
        }
    }
}
